import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.text.SimpleDateFormat;
import java.util.Date;

/**
 * PreparedStatement
 *  测试 mysql curd
 * @author shengren.yan
 * @create 2023-08-25
 */
public class t2 {

    /**
     * 测试
     *
     * @param args arg游戏
     */
    public static void main(String[] args) {
        t4();
    }

    /**
     * 查询
     */
    private static void t4() {
        String sql = "select bid,pay from bill where bid = ?";
        Bill customer = queryFor(sql, 13);
        System.out.println(customer);
    }

    /**
     * 查询
     *
     * @param sql  sql
     * @param args arg游戏
     * @return {@link Bill}
     */
    public static Bill queryFor(String sql, Object... args) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
            conn = JDBCUtils.getConnection();
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            resultSet = ps.executeQuery();
            //获取结果集的元数据
            ResultSetMetaData metaData = resultSet.getMetaData();
            //通过ResultSetMetaData获取结果集中的列数
            int columnCount = metaData.getColumnCount();
            if (resultSet.next()) {
                Bill bill = new Bill();
                //处理结果集一行数据中的每一个列
                for (int i = 0; i < columnCount; i++) {
                    Object value = resultSet.getObject(i + 1);
                    //获取每个列的列名
                    String columnName = metaData.getColumnName(i + 1);
                    Field field = Bill.class.getDeclaredField(columnName);
                    field.setAccessible(true);
                    field.set(bill, value);
                }
                //有数据才返回
                return bill;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, ps, resultSet);
        }
        return null;
    }

    /**
     * 删除
     */
    private static void t3() {
        Connection connection = null;
        PreparedStatement ps = null;
        try {
            //1.获取数据库的连接
            connection = JDBCUtils.getConnection();
            String sql="delete  from user where id= ?";
            JDBCUtils.change(sql,54);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 关闭资源
            JDBCUtils.closeResource(connection, ps);
        }
    }

    /**
     * 新增
     */
    private static void t2() {
        Connection connection = null;
        PreparedStatement ps = null;
        try {
            //1.获取数据库的连接
            connection = JDBCUtils.getConnection();
            String sql = "insert into user(username,real_name,gender,end_time)values(?,?,?,?)"; //占位符
            ps = connection.prepareStatement(sql);

            ps.setString(1, "哪吒");
            ps.setString(2, "hh");
            ps.setString(3, "1");
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            Date date = sdf.parse("1000-01-01");
            ps.setDate(4, new java.sql.Date(date.getTime()));
            // 执行操作
            ps.execute();
            System.out.println("插入成功");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //关闭资源
            JDBCUtils.closeResource(connection, ps);
        }
    }

    /**
     * 修改
     */
    private static void c1() {
        Connection connection = null;
        PreparedStatement ps = null;
        try {
            //1.获取数据库的连接
            connection = JDBCUtils.getConnection();
            //2.预编译sql语句，返回PreparedStatement实例
            String sql = "update bill set bill_name = ? where bid = ?";
            ps = connection.prepareStatement(sql);
            //3.填充占位符
            ps.setObject(1, "莫扎特");
            ps.setObject(2, 18);
            //4.执行
            ps.execute();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //5.关闭资源
            JDBCUtils.closeResource(connection, ps);
        }
    }


}
